﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Collections;
using System.Data;

namespace clmbine.database.sqlite
{
    public class SqliteDB
    {
        string connectionString = "";
        SQLiteConnection sqlite;
        public SqliteDB(string SqlPath)
        {
            connectionString = SqlPath;
            sqlite = new SQLiteConnection(connectionString);
        }
        public SqliteDB()
        {
            string connectionString = @"C:\Users\방성원\Documents\Visual Studio 2010\Projects\mbine.todo\clmbine.database.sqlite\mbine.todo.dbs";
            sqlite = new SQLiteConnection(connectionString);
        }
        //쿼리 실행
        public bool Exec(string Query)
        {
            try
            {
                Open();
                SQLiteTransaction transaction = sqlite.BeginTransaction();
                SQLiteCommand command = new SQLiteCommand(sqlite);
                command.CommandText = Query;
                command.ExecuteNonQuery();
                transaction.Commit();
                command.Dispose();
                Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
        //쿼리 실행 결과(단일 String) 가져오기
        public String QueryValue(string Query)
        {
            try
            {
                String returnValue = "";
                Open();
                SQLiteCommand command = new SQLiteCommand(sqlite);
                command.CommandText = Query;
                returnValue = (command.ExecuteScalar() == System.DBNull.Value) ? "" : command.ExecuteScalar().ToString();
                command.Dispose();
                Close();
                return returnValue;
            }
            catch
            {
                return null;
            }
        }
        //쿼리 DataSet 가져오기
        public ArrayList Query(string Query)
        {
            try
            {
                ArrayList rowList = new ArrayList();
                Open();
                SQLiteCommand command = new SQLiteCommand(sqlite);
                command.CommandText = Query;
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    object[] values = new object[reader.FieldCount];
                    reader.GetValues(values);
                    rowList.Add(values);
                }
                reader.Close();
                reader.Dispose();
                command.Dispose();
                Close();
                return rowList;
            }
            catch
            {
                return null;
            }
        }
        //DB Open
        public void Open()
        {
            if (sqlite.State != ConnectionState.Open)
            {
                sqlite.Open();
            }
        }
        //DB Close
        public void Close()
        {
            if (sqlite.State == ConnectionState.Open)
            {
                sqlite.Close();
            }
        }
    }
}